package al.jdbc.DAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import al.jdbc.dpbridge.DataSource;
import al.jdbc.dpbridge.MySqlDataSource;
import alOmrane.jdbc.beans.Courrier_arrive;
import alOmrane.jdbc.beans.Courrier_depart;

public class Courrier_departDAO {
	private Connection db;
	@SuppressWarnings("unused")
	private DataSource ds;
	private OrganigrammeDAO odao = new OrganigrammeDAO(new MySqlDataSource("jdbc:mysql://localhost/gestion_courriers", "root"));
	private Moy_envDAO mdao = new Moy_envDAO(new MySqlDataSource("jdbc:mysql://localhost/gestion_courriers", "root"));
	private Statement sql;
	
	public Courrier_departDAO(DataSource ds){
		db = ds.getConnection();
		this.ds = ds;
		try {
			sql = db.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public List<Courrier_depart> findAll(){
		List<Courrier_depart> cra = new ArrayList<Courrier_depart>();
		try {
			String query = "SELECT * FROM courrier_depart";

			ResultSet rs = sql.executeQuery(query);
			while (rs.next()) {
				Courrier_depart ca = new Courrier_depart(Integer.parseInt(rs.getString(1)),
						rs.getDate(2),//date
						rs.getString(3),//ref_courrier 
						(rs.getString(4)==null)?0:Integer.parseInt(rs.getString(4)), //frais
						rs.getString(5),//objet
						rs.getString(6),//observation
						(rs.getString(7)==null)?0:Integer.parseInt(rs.getString(7)),//delai
						rs.getString(8),//etat
						odao.findById(Integer.parseInt(rs.getString(9))),//organigramme
						mdao.findById(Integer.parseInt(rs.getString(10))));//moyen d'envoi
				cra.add(ca);
			}
			return cra;
		}catch(Exception e){
			System.out.println(e.getMessage());
			return null;
		}
	}
	public Courrier_depart findById(int id){
		try {
			String query = "SELECT * FROM courrier_depart" +
					" WHERE numero_courrier_depart="+id;

			ResultSet rs = sql.executeQuery(query);
			rs.next();
			Courrier_depart ca = new Courrier_depart(Integer.parseInt(rs.getString(1)),
					rs.getDate(2),//date
					rs.getString(3),//ref_courrier 
					(rs.getString(4)==null)?0:Integer.parseInt(rs.getString(4)), //frais
					rs.getString(5),//objet
					rs.getString(6),//observation
					(rs.getString(7)==null)?0:Integer.parseInt(rs.getString(7)),//delai
					rs.getString(8),//etat
					odao.findById(Integer.parseInt(rs.getString(9))),//organigramme
					mdao.findById(Integer.parseInt(rs.getString(10))));//moyen d'envoi
			return ca;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
	
	public boolean insert(Courrier_depart c){
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		try{

			StringBuffer sb = new StringBuffer("INSERT INTO courrier_depart VALUES ('" +
					c.getId()+"','"+
					((c.getDate_envoi()==null)?"0000-00-00":df.format(c.getDate_envoi()))+"','"+
					c.getRef_courrier_depart()+"','"+
					c.getFrais_courrier_depart()+"','"+
					c.getObjet_courrier_depart()+"','"+
					c.getObserv_courrier_depart()+"','"+
					c.getDelai_courrier_depart()+"','"+
					c.getEtat_courrier_depart()+"','"+
					c.getOrganigrame().getId()+"','"+
					c.getMoy_env().getId()+"');");
			sql.executeUpdate(sb.toString());
			return true;
		}catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
	}
	
	public boolean update(Courrier_depart c){
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		try{

			StringBuffer sb = new StringBuffer("UPDATE courrier_depart SET " +
					"date_env='"+((c.getDate_envoi()==null)?"0000-00-00":df.format(c.getDate_envoi()))+"',"+
					"objet_courrier_depart='"+c.getObjet_courrier_depart()+"',"+
					"ref_courrier_depart='"+c.getRef_courrier_depart()+"',"+
					"observ_courrier_depart='"+c.getObserv_courrier_depart()+"',"+
					"etat_courrier_depart='"+c.getEtat_courrier_depart()+"',"+
					"delais_courrier_depart='"+c.getDelai_courrier_depart()+"',"+
					"frais_courrier_depart='"+c.getFrais_courrier_depart()+"',"+
					"id_organigramme='"+c.getOrganigrame().getId()+"',"+
					"id_moy_env='"+c.getMoy_env().getId()+"' " +
							"WHERE numero_courrier_depart="+c.getId());
			sql.executeUpdate(sb.toString());
			return true;
		}catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
	}
	
	public int incrementEngine(){
		try {
			String query = "SELECT Max(numero_courrier_depart) FROM courrier_depart";

			ResultSet rs = sql.executeQuery(query);
			rs.next();
			return Integer.parseInt((rs.getString(1)==null)?"0":rs.getString(1))+1;
		}catch(Exception e){
			e.printStackTrace();
			return 0;
		}
	}
	
	public boolean delete(int idCourrier){
		try{

			StringBuffer sb = new StringBuffer("DELETE FROM courrier_depart "+
					"WHERE numero_courrier_depart="+idCourrier+" ;");
			sql.executeUpdate(sb.toString());
			return true;
		}catch(Exception ex){
			System.out.println(ex.getMessage());
			return false;
		}
	}
	
	public boolean delete(Courrier_depart c){
		return delete(c.getId());
	}
}
